﻿using System;
using System.Data;
using System.Collections.Generic;
using System.Data.SqlClient;
using YiTu.DBUtility;
namespace TOUR.SZHOME.DAL
{
    /// <summary>
    /// 数据表Projects_Unit的数据操作类
    /// </summary>
    public partial class Projects_UnitDAL
    {
        #region ConstVariables
        private const string C_TABLE_NAME = "Projects_Unit";
        private const string C_SP_PROJECTS_UNIT_FIELDS = "[HXID],[XMID],[SFZTHX],[HXMS],[HXMJ],[HXJJ],[XMLX],[XMHX],[HXYT]";
        private const string C_SP_PROJECTS_UNIT_INSERT = "INSERT INTO [Projects_Unit]([XMID],[SFZTHX],[HXMS],[HXMJ],[HXJJ],[XMLX],[XMHX],[HXYT]) VALUES(@XMID,@SFZTHX,@HXMS,@HXMJ,@HXJJ,@XMLX,@XMHX,@HXYT);SET @HXID = SCOPE_IDENTITY();";
        private const string C_SP_PROJECTS_UNIT_UPDATE = "UPDATE [Projects_Unit] SET [XMID]=@XMID,[SFZTHX]=@SFZTHX,[HXMS]=@HXMS,[HXMJ]=@HXMJ,[HXJJ]=@HXJJ,[XMLX]=@XMLX,[XMHX]=@XMHX,[HXYT]=@HXYT WHERE [HXID] = @HXID";
        private const string C_SP_PROJECTS_UNIT_DELETE = "DELETE [Projects_Unit] WHERE [HXID] = @HXID";
        private const string C_SP_PROJECTS_UNIT_GET = "SELECT [HXID],[XMID],[SFZTHX],[HXMS],[HXMJ],[HXJJ],[XMLX],[XMHX],[HXYT] FROM [Projects_Unit] WHERE [HXID] = @HXID";
        #endregion

        private static string ConnectionString
        {
            get
            {
                return System.Configuration.ConfigurationManager.ConnectionStrings["TravelProject"].ConnectionString;
            }
        }

        /// <summary>
        /// 默认构造函数
        /// </summary>
        private Projects_UnitDAL() { }

        /// <summary>
        /// 向数据表中插入一条新记录
        /// </summary>
        /// <param name="entity">Entity.Projects_UnitEntity实体类</param>
        /// <remarks>如果表存在自增长字段，插入记录成功后自增长字段值会更新至实体</remarks>
        public static bool Insert(Entity.Projects_UnitEntity entity)
        {
            List<SqlParameter> commandParms = new List<SqlParameter>();
            SqlParameter id_HXID = SqlHelper.CreateParam("@HXID", SqlDbType.Int, 0, ParameterDirection.Output, null);
            commandParms.Add(id_HXID);
            commandParms.Add(SqlHelper.CreateParam("@XMID", SqlDbType.Int, 0, ParameterDirection.Input, entity.XMID));
            commandParms.Add(SqlHelper.CreateParam("@SFZTHX", SqlDbType.Bit, 0, ParameterDirection.Input, entity.SFZTHX));
            commandParms.Add(SqlHelper.CreateParam("@HXMS", SqlDbType.NVarChar, 200, ParameterDirection.Input, entity.HXMS));
            commandParms.Add(SqlHelper.CreateParam("@HXMJ", SqlDbType.NVarChar, 50, ParameterDirection.Input, entity.HXMJ));
            commandParms.Add(SqlHelper.CreateParam("@HXJJ", SqlDbType.Decimal, 0, ParameterDirection.Input, entity.HXJJ));
            commandParms.Add(SqlHelper.CreateParam("@XMLX", SqlDbType.NVarChar, 100, ParameterDirection.Input, entity.XMLX));
            commandParms.Add(SqlHelper.CreateParam("@XMHX", SqlDbType.NVarChar, 100, ParameterDirection.Input, entity.XMHX));
            commandParms.Add(SqlHelper.CreateParam("@HXYT", SqlDbType.NVarChar, 200, ParameterDirection.Input, entity.HXYT));
            
            return SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, C_SP_PROJECTS_UNIT_INSERT, commandParms) > 0 ? true : false;
            //entity.HXID = Convert.ToInt32(id_HXID.Value);
        }

        /// <summary>
        /// 获取数据库一条记录实体(根据主键条件)
        /// </summary>
        /// <param name="hXID">户型ID</param>
        /// <returns>Entity.Projects_UnitEntity实体类</returns>
        public static Entity.Projects_UnitEntity GetByHXID(int hXID)
        {
            Entity.Projects_UnitEntity entity = null;
            List<SqlParameter> commandParms = new List<SqlParameter>();
            commandParms.Add(SqlHelper.CreateParam("@HXID", SqlDbType.Int, 0, ParameterDirection.Input, hXID));

            using (SqlDataReader reader = SqlHelper.ExecuteReader(ConnectionString, CommandType.Text, C_SP_PROJECTS_UNIT_GET, commandParms))
            {
                if (reader.Read())
                {
                    entity = ConvertToEntityFromDataReader(reader);
                }
            }

            return entity;
        }

        /// <summary>
        /// 更新数据库中一条记录(根据主键条件)
        /// </summary>
        /// <param name="entity">Entity.Projects_UnitEntity实体类</param>
        public static bool Update(Entity.Projects_UnitEntity entity)
        {
            List<SqlParameter> commandParms = new List<SqlParameter>();

            commandParms.Add(SqlHelper.CreateParam("@HXID", SqlDbType.Int, 0, ParameterDirection.Input, entity.HXID));
            commandParms.Add(SqlHelper.CreateParam("@XMID", SqlDbType.Int, 0, ParameterDirection.Input, entity.XMID));
            commandParms.Add(SqlHelper.CreateParam("@SFZTHX", SqlDbType.Bit, 0, ParameterDirection.Input, entity.SFZTHX));
            commandParms.Add(SqlHelper.CreateParam("@HXMS", SqlDbType.NVarChar, 200, ParameterDirection.Input, entity.HXMS));
            commandParms.Add(SqlHelper.CreateParam("@HXMJ", SqlDbType.NVarChar, 50, ParameterDirection.Input, entity.HXMJ));
            commandParms.Add(SqlHelper.CreateParam("@HXJJ", SqlDbType.Decimal, 0, ParameterDirection.Input, entity.HXJJ));
            commandParms.Add(SqlHelper.CreateParam("@XMLX", SqlDbType.NVarChar, 100, ParameterDirection.Input, entity.XMLX));
            commandParms.Add(SqlHelper.CreateParam("@XMHX", SqlDbType.NVarChar, 100, ParameterDirection.Input, entity.XMHX));
            commandParms.Add(SqlHelper.CreateParam("@HXYT", SqlDbType.NVarChar, 200, ParameterDirection.Input, entity.HXYT));

            return SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, C_SP_PROJECTS_UNIT_UPDATE, commandParms) > 0 ? true : false;
        }

        /// <summary>
        /// 删除数据库中一条记录(根据主键条件)
        /// </summary>
        /// <param name="hXID">户型ID</param>
        public static bool Delete(int hXID)
        {
            List<SqlParameter> commandParms = new List<SqlParameter>();
            commandParms.Add(SqlHelper.CreateParam("@HXID", SqlDbType.Int, 0, ParameterDirection.Input, hXID));
            return SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, C_SP_PROJECTS_UNIT_DELETE, commandParms) > 0 ? true : false;
        }

        /// <summary>
        /// 按条件查询数据表,返回DataTable类型数据
        /// </summary>
        /// <param name="whereClause">SQL条件语句(可为空)，不须带"Where"关键字</param>
        /// <param name="dataFields">需返回字段(不能为空,为"*"则返回所有字段)</param>
        /// <param name="orderBy">SQL排序语句(不能为空)，不须带"Order By"关键字</param>
        /// <param name="startRowIndex">记录开始索引，从0开始</param>
        /// <param name="maximumRows">返回记录数量</param>
        /// <returns>DataTable</returns>
        public static DataTable SearchDT(string whereClause, string dataFields, string orderBy, int startRowIndex, int maximumRows)
        {
            if (dataFields.Trim() == "*")
            {
                dataFields = C_SP_PROJECTS_UNIT_FIELDS;
            }
            return SqlListHepler.Search(ConnectionString, C_TABLE_NAME, dataFields, whereClause, orderBy, startRowIndex, maximumRows);
        }


        /// <summary>
        /// 按条件查询数据表,返回 Entity.Projects_UnitEntity 数据集
        /// </summary>
        /// <param name="whereClause">SQL条件语句(可为空)，不须带"Where"关键字</param>
        /// <param name="orderBy">SQL排序语句(不能为空)，不须带"Order By"关键字</param>
        /// <param name="startRowIndex">记录开始索引，从0开始</param>
        /// <param name="maximumRows">返回记录数量</param>
        public static List<Entity.Projects_UnitEntity> Search(string whereClause, string orderBy, int startRowIndex, int maximumRows)
        {
            List<Entity.Projects_UnitEntity> list = new List<Entity.Projects_UnitEntity>();
            using (SqlDataReader reader = SqlListHepler.SearchDataReader(ConnectionString, C_TABLE_NAME, C_SP_PROJECTS_UNIT_FIELDS, whereClause, orderBy, startRowIndex, maximumRows))
            {
                while (reader.Read())
                {
                    list.Add(ConvertToEntityFromDataReader(reader));
                }
            }
            return list;
        }

        /// <summary>
        /// 按条件查询数据表,返回DataTable类型数据
        /// </summary>
        /// <param name="whereClause">SQL条件语句(可为空)，不须带"Where"关键字</param>
        /// <param name="dataFields">需返回字段(不能为空,为"*"则返回所有字段)</param>
        /// <param name="orderBy">SQL排序语句(可为空)，不须带"Order By"关键字</param>
        /// <param name="rowsToReturn">返回记录数量</param>
        /// <returns>DataTable</returns>
        public static DataTable SearchDT(string whereClause, string dataFields, string orderBy, int rowsToReturn)
        {
            if (dataFields.Trim() == "*")
            {
                dataFields = C_SP_PROJECTS_UNIT_FIELDS;
            }
            return SqlListHepler.Search(ConnectionString, C_TABLE_NAME, dataFields, whereClause, orderBy, rowsToReturn);
        }

        /// <summary>
        /// 按条件查询数据表,返回 Entity.Projects_UnitEntity 数据集
        /// </summary>
        /// <param name="whereClause">SQL条件语句(可为空)，不须带"Where"关键字</param>
        /// <param name="orderBy">SQL排序语句(可为空)，不须带"Order By"关键字</param>
        /// <param name="rowsToReturn">返回记录数量</param>
        public static List<Entity.Projects_UnitEntity> Search(string whereClause, string orderBy, int rowsToReturn)
        {
            List<Entity.Projects_UnitEntity> list = new List<Entity.Projects_UnitEntity>();
            using (SqlDataReader reader = SqlListHepler.SearchDataReader(ConnectionString, C_TABLE_NAME, C_SP_PROJECTS_UNIT_FIELDS, whereClause, orderBy, rowsToReturn))
            {
                while (reader.Read())
                {
                    list.Add(ConvertToEntityFromDataReader(reader));
                }
            }
            return list;
        }

        /// <summary>
        /// 按条件获取记录数量
        /// </summary>
        /// <param name="whereClause">SQL条件语句(可为空)，不须带"Where"关键字</param>
        /// <returns>int整型数据</returns>
        public static int SearchCount(string whereClause)
        {
            return SqlListHepler.SearchCount(ConnectionString, C_TABLE_NAME, whereClause);
        }

        /// <summary>
        /// 转换DataRow类型数据记录为实体
        /// </summary>
        private static Entity.Projects_UnitEntity ConvertToEntityFromDataRow(DataRow row)
        {
            Entity.Projects_UnitEntity entity = new Entity.Projects_UnitEntity();
            entity.HXID = Convert.ToInt32(row["HXID"]);
            entity.XMID = Convert.ToInt32(row["XMID"]);
            entity.SFZTHX = Convert.ToBoolean(row["SFZTHX"]);
            if (Convert.IsDBNull(row["HXMS"])) entity.HXMS = null; else entity.HXMS = row["HXMS"].ToString();
            if (Convert.IsDBNull(row["HXMJ"])) entity.HXMJ = null; else entity.HXMJ = row["HXMJ"].ToString();
            if (Convert.IsDBNull(row["HXJJ"])) entity.HXJJ = null; else entity.HXJJ = Convert.ToDecimal(row["HXJJ"]);
            if (Convert.IsDBNull(row["XMLX"])) entity.XMLX = null; else entity.XMLX = row["XMLX"].ToString();
            if (Convert.IsDBNull(row["XMHX"])) entity.XMHX = null; else entity.XMHX = row["XMHX"].ToString();
            if (Convert.IsDBNull(row["HXYT"])) entity.HXYT = null; else entity.HXYT = row["HXYT"].ToString();

            return entity;
        }

        /// <summary>
        /// 转换SqlDataReader类型数据记录为实体
        /// </summary>
        private static Entity.Projects_UnitEntity ConvertToEntityFromDataReader(SqlDataReader reader)
        {
            Entity.Projects_UnitEntity entity = new Entity.Projects_UnitEntity();
            entity.HXID = Convert.ToInt32(reader["HXID"]);
            entity.XMID = Convert.ToInt32(reader["XMID"]);
            entity.SFZTHX = Convert.ToBoolean(reader["SFZTHX"]);
            if (Convert.IsDBNull(reader["HXMS"])) entity.HXMS = null; else entity.HXMS = reader["HXMS"].ToString();
            if (Convert.IsDBNull(reader["HXMJ"])) entity.HXMJ = null; else entity.HXMJ = reader["HXMJ"].ToString();
            if (Convert.IsDBNull(reader["HXJJ"])) entity.HXJJ = null; else entity.HXJJ = Convert.ToDecimal(reader["HXJJ"]);
            if (Convert.IsDBNull(reader["XMLX"])) entity.XMLX = null; else entity.XMLX = reader["XMLX"].ToString();
            if (Convert.IsDBNull(reader["XMHX"])) entity.XMHX = null; else entity.XMHX = reader["XMHX"].ToString();
            if (Convert.IsDBNull(reader["HXYT"])) entity.HXYT = null; else entity.HXYT = reader["HXYT"].ToString();

            return entity;
        }

    }
}
